﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CodeCamp12.Domain;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using log4net;

namespace CodeCamp12.Domain
{
    public class SpeakerDataManagement
    {
        private ConnectionStringProvider connString = new ConnectionStringProvider();
        private ILog log = LogManager.GetLogger("CodeCamp12.DAL");

        /// <summary>
        /// Inserts new speaker in the database
        /// </summary>
        /// <param name="speaker"></param>
        /// <returns></returns>
        public Guid InsertSpeaker(Speaker speaker)
        {
            Guid id;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("InsertSpeaker", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@FirstName", speaker.FirstName);
            comm.Parameters.AddWithValue("@LastName", speaker.LastName);
            comm.Parameters.AddWithValue("@CompanyName", speaker.CompanyName);
            comm.Parameters.AddWithValue("@Country", speaker.Country);
            comm.Parameters.AddWithValue("@ExpertIn", speaker.ExpertIN);
            comm.Parameters.AddWithValue("@Link", speaker.Link);
            comm.Parameters.AddWithValue("@Description", speaker.Description);

            try
            {
                conn.Open();
                id = (Guid) comm.ExecuteScalar();
            }
            catch(Exception ex)
            {
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

            return id;

        }

        /// <summary>
        /// Deletes speaker from database
        /// </summary>
        /// <param name="speaker"></param>
        public void DeleteSpeaker(Speaker speaker)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("DeleteSpeaker", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@id", speaker.ID);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Updates speaker's data in the database
        /// </summary>
        /// <param name="speaker"></param>
        public void UpdateSpeaker(Speaker speaker)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("UpdateSpeaker", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@ID", speaker.ID);
            comm.Parameters.AddWithValue("@FirstName", speaker.FirstName);
            comm.Parameters.AddWithValue("@LastName", speaker.LastName);
            comm.Parameters.AddWithValue("@CompanyName", speaker.CompanyName);
            comm.Parameters.AddWithValue("@Country", speaker.Country);
            comm.Parameters.AddWithValue("@ExpertIn", speaker.ExpertIN);
            comm.Parameters.AddWithValue("@Link", speaker.Link);
            comm.Parameters.AddWithValue("@Description", speaker.Description);
            
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }   
        }

        /// <summary>
        /// Returns a list with all the speakers from the database
        /// </summary>
        /// <returns>Speaker</returns>
        public List<Speaker> GetAllSpeakers()
        {
            
            List<Speaker> speakerList = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetAllSpeakers") { CommandType = System.Data.CommandType.StoredProcedure };
            comm.Connection = conn;
            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                while (reader.Read())
                {

                    if (speakerList == null)
                    {
                        speakerList = new List<Speaker>();
                    }

                    var speaker = new Speaker
                    {
                        ID = new Guid(reader["ID"].ToString()),
                        FirstName = reader["FirstName"].ToString(),
                        LastName = reader["LastName"].ToString(),
                        CompanyName = reader["CompanyName"].ToString(),
                        Country = reader["Country"].ToString(),
                        ExpertIN = reader["ExpertIn"].ToString(),
                        Link = reader["Link"].ToString(),
                        Description = reader["Description"].ToString()
                    };
                    speakerList.Add(speaker);
                }
                reader.Close();

            }
            catch (Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

            return speakerList;
        }

        /// <summary>
        /// Get all speakers for event with id passed in parametars
        /// </summary>
        /// <param name="eventId"></param>
        /// <returns></returns>
        public List<Speaker> GetSpeakersForEvent(Guid eventId)
        {
            List<Speaker> speakerList = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetSpeakersForEvent") { CommandType = System.Data.CommandType.StoredProcedure };
            comm.Parameters.AddWithValue("@id", eventId);
            comm.Connection = conn;
            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    if (speakerList == null)
                    {
                        speakerList = new List<Speaker>();
                    }

                    var speaker = new Speaker
                    {
                        ID = new Guid(reader["ID"].ToString()),
                        FirstName = reader["FirstName"].ToString(),
                        LastName = reader["LastName"].ToString(),
                        CompanyName = reader["CompanyName"].ToString(),
                        Country = reader["Country"].ToString(),
                        ExpertIN = reader["ExpertIn"].ToString(),
                        Link = reader["Link"].ToString(),
                        Description = reader["Description"].ToString()
                    };
                    speakerList.Add(speaker);
                }
                reader.Close();

            }
            catch (Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

            return speakerList;
        }

        /// <summary>
        /// Returns all speakers for the current event
        /// </summary>
        /// <returns></returns>
        public List<Speaker> GetSpeakersForCurrent()
        {
            List<Speaker> speakerList = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetAllSpeakersForCurrentEvent") { CommandType = System.Data.CommandType.StoredProcedure };
            comm.Connection = conn;
            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    if (speakerList == null)
                    {
                        speakerList = new List<Speaker>();
                    }

                    var speaker = new Speaker
                    {
                        ID = new Guid(reader["ID"].ToString()),
                        FirstName = reader["FirstName"].ToString(),
                        LastName = reader["LastName"].ToString(),
                        CompanyName = reader["CompanyName"].ToString(),
                        Country = reader["Country"].ToString(),
                        ExpertIN = reader["ExpertIn"].ToString(),
                        Link = reader["Link"].ToString(),
                        Description = reader["Description"].ToString()
                    };
                    speakerList.Add(speaker);
                }
                reader.Close();

            }
            catch (Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

            return speakerList;
        }
        
        /// <summary>
        /// Returns the speaker with the current ID
        /// </summary>
        /// <param name="speakerId"></param>
        /// <returns>Speaker</returns>
        public Speaker GetSpeakerById(Guid speakerId)
        {
            Speaker speaker = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetSpeakerByID", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@id", speakerId);
            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    speaker = new Speaker
                                  {
                                      ID = new Guid(reader["ID"].ToString()),
                                      FirstName = reader["FirstName"].ToString(),
                                      LastName = reader["LastName"].ToString(),
                                      CompanyName = reader["CompanyName"].ToString(),
                                      Country = reader["Country"].ToString(),
                                      ExpertIN = reader["ExpertIn"].ToString(),
                                      Link = reader["Link"].ToString(),
                                      Description = reader["Description"].ToString()
                                  };
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
            return speaker;
        }

        /// <summary>
        /// Returns the resume for speaker with id passed as parametar
        /// </summary>
        /// <param name="speakerId"></param>
        /// <returns></returns>
        public Domain.File GetFileForSpeaker(Guid speakerId)
        {
            Domain.File file = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetFileForSpeaker", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@id", speakerId);

            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    file = new Domain.File
                               {
                                   ID = new Guid(reader["FileID"].ToString()),
                                   FileName = reader["FileName"].ToString(),
                                   FileType = reader["FileType"].ToString(),
                                   FileData = new MemoryStream((byte[]) reader["FileData"]),
                                   SpeakerID = speakerId
                               };
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

            return file;

        }

        /// <summary>
        /// Returns the image for speaker with id passed as parametar
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Domain.File GetImageForSpeaker(Guid speakerId)
        {
            Domain.File file = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetImageForSpeaker", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@id", speakerId);

            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    file = new Domain.File
                               {
                                   ID = new Guid(reader["FileID"].ToString()),
                                   FileName = reader["FileName"].ToString(),
                                   FileType = reader["FileType"].ToString(),
                                   FileData = new MemoryStream((byte[]) reader["FileData"]),
                                   SpeakerID = speakerId
                               };
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

            return file;

        }

        /// <summary>
        /// Returns all the speaker where SpeakerName parametar is part of there name
        /// </summary>
        /// <param name="speakerName"></param>
        /// <returns></returns>
        public List<Speaker> GetSpeakersByName(string speakerName)
        {
            List<Speaker> speakerList = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetSpeakersByName") { CommandType = System.Data.CommandType.StoredProcedure };
            comm.Parameters.AddWithValue("@firstname", speakerName);
            comm.Connection = conn;
            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    if (speakerList == null)
                    {
                        speakerList = new List<Speaker>();
                    }

                    var speaker = new Speaker
                    {
                        ID = new Guid(reader["ID"].ToString()),
                        FirstName = reader["FirstName"].ToString(),
                        LastName = reader["LastName"].ToString(),
                        CompanyName = reader["CompanyName"].ToString(),
                        Country = reader["Country"].ToString(),
                        ExpertIN = reader["ExpertIn"].ToString(),
                        Link = reader["Link"].ToString(),
                        Description = reader["Description"].ToString()
                    };
                    speakerList.Add(speaker);
                }
                reader.Close();

            }
            catch (Exception ex)
            {

                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

            return speakerList;
        }
    }
}
